using System;
using System.Collections.Generic;
using System.Data;
using Sixeyed.CodeGeneration.Metadata.Bases;
using Sixeyed.CodeGeneration.Metadata.Database.Providers;

namespace Sixeyed.CodeGeneration.Metadata.Database
{
    /// <summary>
    /// Abstract base class for database metadata sources
    /// </summary>
    /// <typeparam name="TConnection">Type of IDbConnection</typeparam>
    /// <typeparam name="TCommand">Type of IDbCommand</typeparam>
    /// <typeparam name="TAdapter">Type of IDbDataAdapter</typeparam>
    public abstract class DatabaseSource<TConnection, TCommand, TAdapter> : SourceBase, IDatabaseSource, IDisposable
        where TConnection : IDbConnection, new()
        where TCommand : IDbCommand
        where TAdapter : IDbDataAdapter, new()
    {
        #region Private instance fields

        private string _connectionString;
        private TConnection _connection;

        #endregion

        #region Instance properties

        /// <summary>
        /// Database connection string
        /// </summary>
        public string ConnectionString
        {
            get { return _connectionString; }
            set 
            {
                if (this._connectionString != value)
                {
                    _connectionString = value;
                    this._connection = default(TConnection);
                }
            }
        }

        /// <summary>
        /// Display name for source Uri property
        /// </summary>
        public override string UriDisplayName
        {
            get { return "Connection String"; }
        }

        /// <summary>
        /// Database connection
        /// </summary>
        protected TConnection Connection
        {
            get
            {
                if (this._connection == null)
                {
                    this._connection = new TConnection();
                    this._connection.ConnectionString = this.ConnectionString;
                }
                if (this._connection.State != ConnectionState.Open)
                {
                    this._connection.Open();
                }
                return this._connection;
            }
        }

        /// <summary>
        /// Database connection string used as source Uri
        /// </summary>
        public override string Uri
        {
            get { return this.ConnectionString; }
            set { this.ConnectionString = value; }
        }

        /// <summary>
        /// Gets the metadata source type name
        /// </summary>
        public override string SourceTypeName
        {
            get { return "Database"; }
        } 

        #endregion

        #region Constructors

        /// <summary>
        /// Default constructor
        /// </summary>
        public DatabaseSource() { }

        /// <summary>
        /// Constructor with initial state
        /// </summary>
        /// <param name="connectionString">Database connection string</param>
        public DatabaseSource(string connectionString)
        {
            this._connectionString = connectionString;
        }

        #endregion

        #region Abstract members

        /// <summary>
        /// Gets the SQL string to provide a list of stored procedures
        /// </summary>
        public abstract string StoredProcedureListSql { get; }

        /// <summary>
        /// Gets the SQL string to provide a list of database tables
        /// </summary>
        public abstract string TableListSql { get; }

        /// <summary>
        /// Gets the database command to return stored procedure metadata
        /// </summary>
        /// <param name="storedProcedureName">Name of stored procedure</param>
        /// <returns>Populated IDbCommand</returns>
        public abstract IDbCommand GetStoredProcedureCommand(string storedProcedureName);

        /// <summary>
        /// Gets the database command to return table metadata
        /// </summary>
        /// <param name="tableName">Name of table</param>
        /// <returns>Populated IDbCommand</returns>
        public abstract IDbCommand GetTableCommand(string tableName);

        #endregion
        
        #region Instance methods

        /// <summary>
        /// Returns a data reader populated with the result of the SQL command
        /// </summary>
        /// <param name="commandText">SQL command to execute</param>
        /// <returns>Populated IDataReader</returns>
        public IDataReader ExecuteReader(string commandText)
        {
            IDbCommand command = this.Connection.CreateCommand();
            command.CommandType = CommandType.Text;
            command.CommandText = commandText;
            return command.ExecuteReader();
        }

        /// <summary>
        /// Returns a list of item names from a SQL command
        /// </summary>
        /// <param name="sqlText">SQL command to execute</param>
        /// <returns>List of strings returned from SQL</returns>
        public List<string> GetItemNames(string sqlText)
        {
            List<string> itemNames = new List<string>();
            using (IDataReader reader = this.ExecuteReader(sqlText))
            {
                while (reader.Read())
                {
                    itemNames.Add(reader.GetString(0));
                }
                reader.Close();
            }
            return itemNames;
        }       

        /// <summary>
        /// Returns a list of item names of the given type
        /// </summary>
        /// <param name="itemType">Database item type</param>
        /// <returns>List of database items</returns>
        public List<string> GetItemNames(DatabaseItemType itemType)
        {
            string sqlText = null;
            switch (itemType)
            {
                case DatabaseItemType.StoredProcedure:
                    sqlText = this.StoredProcedureListSql;
                    break;
                case DatabaseItemType.Table:
                    sqlText = this.TableListSql;
                    break;
                default:
                    throw new ArgumentException(string.Format("Cannot provide names for item type: {0}", itemType));
            }
            return this.GetItemNames(sqlText);
        }

        /// <summary>
        /// Returns a database command for loading a metadata item
        /// </summary>
        /// <param name="itemType">Database item type</param>
        /// <param name="itemKey">Item identifier</param>
        /// <returns>Populated IDbCommand</returns>
        public IDbCommand GetItemCommand(DatabaseItemType itemType, string itemKey)
        {
            IDbCommand command = null;
            switch (itemType)
            {
                case DatabaseItemType.StoredProcedure:
                    command = this.GetStoredProcedureCommand(itemKey);
                    break;
                case DatabaseItemType.Table:
                    command = this.GetTableCommand(itemKey);
                    break;
                case DatabaseItemType.QueryResult:
                    command = this.CreateCommand(CommandType.Text, itemKey);
                    break;
                default:
                    throw new ArgumentException(string.Format("Cannot provide database command for item type: {0}", itemType));
            }
            return command;
        }

        /// <summary>
        /// Returns an initialised database command
        /// </summary>
        /// <param name="commandType">Database command type</param>
        /// <param name="commandFormat">SQL command string format</param>
        /// <param name="commandArgs">SQL command string arguments</param>
        /// <returns>Initialised IDbCommand</returns>
        protected TCommand CreateCommand(CommandType commandType, string commandFormat, params object[] commandArgs)
        {
            string commandText = string.Format(commandFormat, commandArgs);
            TCommand command = (TCommand) this.Connection.CreateCommand();
            command.CommandType = commandType;
            command.CommandText = commandText;
            return command;
        }

        /// <summary>
        /// Returns dataset from executing database command
        /// </summary>
        /// <param name="command">Command to execute</param>
        /// <returns>Populated DataSet</returns>
        public DataSet GetData(IDbCommand command)
        {
            DataSet ds = new DataSet();
            IDbDataAdapter adapter = new TAdapter();
            adapter.SelectCommand = command;
            adapter.Fill(ds);
            return ds;
        }

        /// <summary>
        /// Returns list of data table representing SQL schema
        /// </summary>
        /// <param name="command">Command to execute</param>
        /// <returns>Populated list of DataTables</returns>
        public List<DataTable> GetSchema(IDbCommand command)
        {
            List<DataTable> tables = new List<DataTable>();
            DataSet ds = new DataSet();
            IDbDataAdapter adapter = new TAdapter();
            adapter.SelectCommand = command;
            tables.AddRange(adapter.FillSchema(ds, SchemaType.Source));
            return tables;
        }

        /// <summary>
        /// Disposes database resources
        /// </summary>
        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        /// <summary>
        /// Disposes database resources
        /// </summary>
        /// <param name="disposing">Whether disposing</param>
        protected virtual void Dispose(bool disposing)
        {
            if (disposing)
            {
                if (this._connection != null)
                {
                    if (this._connection.State == ConnectionState.Open)
                    {
                        this._connection.Close();
                    }
                    this._connection.Dispose();
                }
            }
        }

        #endregion
    }
}
